Release 10.1A: OpenEdge Development:
Progress 4GL Handbook

Identifying the current row in the query

As you move through the results list, Progress keeps track of the current row number, that is, the sequence of the row in the results list. You can retrieve this value using the CURRENT-RESULT-ROW function:

CURRENT-RESULT-ROW ( query-name )

The function returns an INTEGER value with the sequence of the current row. The query-name is an expression, either a quoted query name or a variable reference.

For CURRENT-RESULT-ROW to work properly, you must define the query to be SCROLLING. If you don’t define the query as SCROLLING, the CURRENT-RESULT-ROW function returns a value, but that value is not reliable.

To use CURRENT-RESULT-ROW, make these changes to your sample procedure:

DEFINE QUERY CustQuery FOR Customer SCROLLING. OPEN QUERY CustQuery FOR EACH Customer WHERE State = "LA". GET FIRST CustQuery. DO WHILE NOT QUERY-OFF-END("CustQuery"):     DISPLAY Customer.CustNum Customer.NAME             NUM-RESULTS("CustQuery") LABEL "Rows"             CURRENT-RESULT-ROW("CustQuery") LABEL "Row#"         WITH FRAME CustFrame 15 DOWN.     GET NEXT CustQuery.     DOWN WITH FRAME CustFrame. END.

When you run the procedure, you see that the value of CURRENT-RESULT-ROW keeps pace with NUM-RESULTS, as shown in Figure 10–4.

Figure 10–4: Result of CURRENT-RESULT-ROW example

This is not always the case, of course. If you use the PRESELECT option or a nonindexed sort to retrieve the data, then NUM-RESULTS is always 13, as you have seen. But the value of CURRENT-RESULT-ROW changes from 1 to 13 just as it does above.

You can use the CURRENT-RESULT-ROW function to save off a pointer to reposition to a specific row. See the "Using a RowID to identify a record" section for information on how to identify a record.

Here are a few special cases for CURRENT-RESULT-ROW:

If the query is empty, the function returns the Unknown value (?).
If the query is explicitly positioned before the first row, for example by executing a GET FIRST followed by a GET PREV, then the function returns the value 1.
If the query is explicitly positioned after the last row, for example by executing a GET LAST followed by a GET NEXT, then the function returns the value one more than the number of rows in the results list.

Using INDEXED-REPOSITION to improve query performance

If you anticipate jumping around in the result set using statements such as GET LAST, you should add another option to the end of your OPEN QUERY statement: the INDEXED-REPOSITION keyword. If you do this, your DEFINE QUERY statement must also specify the SCROLLING keyword.

If you don’t open the query with INDEXED-REPOSITION, then Progress retrieves all records in sequence in order to satisfy a request such as GET LAST. This can be very costly. If you do use INDEXED-REPOSITION, Progress uses indexes, if possible, to jump directly to a requested row, greatly improving performance in some cases. There are side effects to doing this, however, in terms of the integrity of the results list, as discussed next.

INDEXED-REPOSITION and field lists

When you define a query with the FIELDS phrase, make sure that you include (in the list of fields) the index that the query will use when you open the query with the INDEXED-REPOSITION keyword. Otherwise, you’ll get an error when the query is re-opened and it attempts to reposition on a field that it cannot find.

Factors that invalidate CURRENT-RESULT-ROW and NUM-RESULTS

Under some circumstances, when you open your query with the INDEXED-REPOSITION keyword, the value of CURRENT-RESULT-ROW or NUM-RESULTS becomes invalid. As explained earlier, the results list holds the row identifiers for those rows that satisfy the query and that have already been retrieved.

Thirteen rows satisfy the query for Customers in Louisiana, so the value of these two functions goes as high as 13 for that query. When you do a PRESELECT or a nonindexed sort, all the rows have already been retrieved before any data is presented to you, so NUM-RESULTS is 13 at the beginning of the DISPLAY loop. Normally, Progress adds the identifiers for all the rows it retrieves to the results list, but there are circumstances where this is not the case. If you execute a GET LAST statement on a query, and your OPEN QUERY statement does not use a PRESELECT or a sort that forces records to be pre-retrieved, Progress jumps directly to the last record using a database index, without cycling through all the records in between. In this case, it has no way of knowing how many records would have been retrieved between the first one and the last one, and it cannot maintain a contiguous results list of all rows that satisfy the query. For this reason, Progress flushes and reinitializes the results list when you jump forward or backward in the query. So after a GET LAST statement, NUM-RESULTS returns 1 (because the GET LAST statement has retrieved one row) and CURRENT-RESULT-ROW is unknown (because there is no way to know where that row would fit into the full results list).